<?php
namespace Home\Controller;
use Think\Controller;
class SalesController extends CommonController {
    public function index(){
        ini_set('memory_limit','2048M');
        $where = array();
        //时间参数
        $start_time = I('start_time');
        $end_time = I('end_time');
        $type = I("type");
        //拼接where条件

        $this->assign('start_time',$start_time);
        $this->assign('end_time',$end_time);

        //昨日时间
        $beginYesterday=mktime(0,0,0,date('m'),date('d')-1,date('Y'));
        $endYesterday=mktime(0,0,0,date('m'),date('d'),date('Y'))-1;

        $member = D("member");

        $page = I('p') ? I('p') : '1';
        $pageSize = 20;
        if($type == 2){    //按店铺统计
            $list = $this->sales_count($start_time,$end_time,$page,$pageSize);
        }elseif($type == 3){//按部门统计
            $list = $this->sales_money($start_time,$end_time,$page,$pageSize);
        }elseif($type == 1){
            //根据当前条件调取不同类型的销售额统计
            $sales_money = $this->sales_money($start_time,$end_time,$page,$pageSize);
            //查询员工的销售量及销售额  并排序
            $list = $this->sonSales($start_time,$end_time,$page,$pageSize);
        }else{
            $list = $this->recommendMember($start_time,$end_time,$page,$pageSize);

        }
        $count = $list['count'];
        $map['start_time'] = $start_time;
        $map['end_time'] = $end_time;
        $map['type'] = $type;
        $show = $this->getPage($count,$page,$pageSize,$map);
        if($this->sessionInfo['parent_id'] == 0){
            $where_main['first_class'] = $this->store_ids;
            //集团拉取人数
            $storeParent = $this->storeTotalMember($start_time,$end_time);
        }else{
            $where_main['second_class'] = $this->store_ids;
        }
        $where_main['fourth_class'] = array("neq",0);
        $where_main['reg_time'] = array("between",array($beginYesterday,$endYesterday));
        //昨日注册人数
        $yesterday = $member->where($where_main)->count();
        //一共注册人数
        $totalMember = $this->totalMember($start_time,$end_time);

        $this->assign('page',$show);
        $this->assign('storeParent',$storeParent);
        $this->assign('totalMember',$totalMember);
        $this->assign('yesterday',$yesterday);
        $this->assign('type',$type);
        $this->assign('totalSales',$list['totalSales']);
        $this->assign('list',$list['list']);

        $this->display();
    }
    //根据条件统计会员列表
    public function recommendMember($start_time,$end_time,$page,$pageSize){
        $member = M("member");
        $store = M("store");
        if($this->sessionInfo['parent_id'] == 0){
            $where_main = array('first_class'=>$this->sessionInfo['user_id'],'fourth_class'=>array("neq",0));
        }else{
            $where_main = array("second_class"=>$this->sessionInfo['user_id'],'fourth_class'=>array('neq',0));
        }
        //拼接where条件
        if($start_time){
            $s_time = strtotime($start_time);
            $where_main['reg_time'] = array("egt",$s_time);
            if($end_time){
                $e_time = strtotime($end_time);
                $where_main['reg_time'] = array("between",array($s_time,$e_time));
            }
        }else if($end_time){
            $e_time = strtotime($end_time);
            $where_main['reg_time'] = array("elt",$e_time);
        }
        $list['count'] = $member
            ->where($where_main)->order('reg_time desc')->count();
        $list['list'] = $member
            ->where($where_main)->order('reg_time desc')->page($page,$pageSize)->select();

        $order = D("order");
        foreach($list['list'] as $k=>$v){
            if(!$v['referid']){
                $list['list'][$k]['referInfo']['user_name'] = '普通注册';
            }else{
                $list['list'][$k]['referInfo'] = $member->where(array('user_id'=>$v['referid']))->find();
            }
            $list['list'][$k]['s_name'] = $store->where(array("store_id"=>$v['first_class']))->getField('store_name');
            if($v['second_class']){
                $list['list'][$k]['s_name'] = $store->where(array("store_id"=>$v['second_class']))->getField('store_name');
            }
            $list['list'][$k]['orderCount'] = $order->where(array("buyer_id"=>$v['user_id'],'status'=>array('in',array(20,30,40))))->count();
            $orderTotal = $order->where(array("buyer_id"=>$v['user_id'],'status'=>array('in',array(20,30,40))))->sum('order_amount');
            $list['list'][$k]['orderTotal'] = $orderTotal?$orderTotal:0.00;

//            $list['totalSales'] += $orderTotal;
        }
        //统计全部金额
        $countList = $member->where($where_main)->order('reg_time desc')->select();
        foreach($countList as $key=>$value){
            $orderTotal = $order->where(array("buyer_id"=>$value['user_id'],'status'=>array('in',array(20,30,40))))->sum('order_amount');
//            $list['list'][$key]['orderTotal'] = $orderTotal?$orderTotal:0.00;
            $list['totalSales'] += $orderTotal;
        }

        $last_names = array_column($list['list'],'orderTotal');
        array_multisort($last_names,SORT_DESC,$list['list']);
        return $list;
    }

    //根据条件统计共注册人数
    private function totalMember($start_time=null,$end_time=null){

        $member = D("member");
        if($this->sessionInfo['parent_id'] == 0){
            $where_main = array('first_class'=>$this->sessionInfo['user_id'],'fourth_class'=>array("neq",0));
        }else{
            $where_main = array("second_class"=>$this->sessionInfo['user_id'],'fourth_class'=>array('neq',0));
        }
        //拼接where条件
        if($start_time){
            $s_time = strtotime($start_time);
            $where_main['reg_time'] = array("egt",$s_time);
            if($end_time){
                $e_time = strtotime($end_time);
                $where_main['reg_time'] = array("between",array($s_time,$e_time));
            }
        }else if($end_time){
            $e_time = strtotime($end_time);
            $where_main['reg_time'] = array("elt",$e_time);
        }
        $totalMember = $member
            ->where($where_main)->count();
        return $totalMember;
    }
  //根据条件统计共集团人数
    private function storeTotalMember($start_time=null,$end_time=null){

        $member = D("member");
        if($this->sessionInfo['parent_id'] == 0){
            $where_main = array('first_class'=>$this->sessionInfo['user_id'],'fourth_class'=>$this->sessionInfo['user_id']);
        }
        //拼接where条件
        if($start_time){
            $s_time = strtotime($start_time);
            $where_main['reg_time'] = array("egt",$s_time);
            if($end_time){
                $e_time = strtotime($end_time);
                $where_main['reg_time'] = array("between",array($s_time,$e_time));
            }
        }else if($end_time){
            $e_time = strtotime($end_time);
            $where_main['reg_time'] = array("elt",$e_time);
        }
        $totalMember = $member
            ->where($where_main)->count();
        return $totalMember;
    }

    //根据条件查询时间段员工的销量  销售额
    private function sonSales($start_time,$end_time,$page,$pageSize){
        $member = D("member");
        $order = M("order");
        $store_relationship = D("store_relationship");

        $con = array(
            'ecm_store_relationship.store_id' => $this->sessionInfo['user_id'] ,
        );
        //说明登录人为总店铺
        if($this->sessionInfo['parent_id'] == 0){
            $con = array('ecm_store_relationship.parent_id'=>$this->sessionInfo['user_id']);
        }
        $count = $store_relationship
            ->join("join ecm_member on ecm_store_relationship.son_id = ecm_member.user_id")
            ->join("join ecm_store on ecm_store_relationship.parent_id = ecm_store.store_id")
            ->field("ecm_member.*,ecm_store.store_name,ecm_store.store_id,ecm_store_relationship.image,ecm_store_relationship.department_name")
            ->where($con)->count();

        $list = $store_relationship
            ->join("join ecm_member on ecm_store_relationship.son_id = ecm_member.user_id")
            ->join("join ecm_store on ecm_store_relationship.parent_id = ecm_store.store_id")
            ->field("ecm_member.*,ecm_store.store_name,ecm_store.store_id,ecm_store_relationship.image,ecm_store_relationship.department_name")
            ->where($con)->page($page,$pageSize)->select();
        $totalSales = 0.00;
        foreach($list as $k=>$v){
            $where['m.third_class'] = $v['user_id'];
            //拼接where条件
            if($start_time){
                $s_time = strtotime($start_time);
                $where['o.pay_time'] = array("egt",$s_time);
                $memberWhere['reg_time'] = array("egt",$s_time);
                if($end_time){
                    $e_time = strtotime($end_time);
                    $where['o.pay_time'] = array("between",array($s_time,$e_time));
                    $memberWhere['reg_time'] = array("between",array($s_time,$e_time));
                }
            }else if($end_time){
                $e_time = strtotime($end_time);
                $where['o.pay_time'] = array("elt",$e_time);
                $memberWhere['reg_time'] = array("elt",$e_time);
            }
            $resultCompleted = $this->statisticalOrder($where,1); //已成交
            $resultDeal      = $this->statisticalOrder($where,2); //未成交 等待买家付款
            $resultCancel    = $this->statisticalOrder($where,3); //已取消
            $resultRefund    = $this->statisticalOrder($where,4); //退款订单

            $memberWhere['fourth_class'] = $v['user_id'];
            $list[$k]['count'] = $member->where($memberWhere)->count();

            $list[$k]['deal_count'] = $resultDeal['count']?$resultDeal['count']:0;
            $list[$k]['deal_money'] = $resultDeal['total']?$resultDeal['total']:0.00;
            $list[$k]['cancel_count'] = $resultCancel['count']?$resultCancel['count']:0;
            $list[$k]['cancel_money'] = $resultCancel['total']?$resultCancel['total']:0.00;
            $list[$k]['refund_count'] = $resultRefund['count']?$resultRefund['count']:0;
            $list[$k]['refund_money'] = $resultRefund['total']?$resultRefund['total']:0.00;
            $list[$k]['sales_count'] = $resultCompleted['count']?$resultCompleted['count']:0;
            $list[$k]['sales_money'] = $resultCompleted['total']?$resultCompleted['total']:0.00;

            $totalSales += $list[$k]['sales_money'];
        }

        $last_names = array_column($list,'sales_money');
        array_multisort($last_names,SORT_DESC,$list);

        $arrData = array('list'=>$list,'totalSales'=>$totalSales,'count'=>$count);
        return $arrData;
    }
    //查询订单统计数目
    private function statisticalOrder($where,$type=1){
        switch ($type){
            case 1:
                $where['o.status'] = array('in',array(20,30,40));//已成交
                break;
            case 2:
                $where['o.status'] = 11;         //未成交 等待买家付款
                break;
            case 3:
                $where['o.status'] = 0;          //已取消
                break;
            case 4:
                $where['o.status'] = 50;         //退款订单
                break;
        }
        $order = M("order");
        $arr['count'] = $order->alias("o")
            ->join("ecm_member m on o.buyer_id = m.user_id")
            ->where($where)
            ->count();
        $arr['total'] = $order->alias('o')
            ->join("ecm_member m on o.buyer_id = m.user_id")
            ->where($where)
            ->sum('o.order_amount');
        return $arr;
    }
    //根据条件调取销售额
    private function sales_money($start_time,$end_time,$page,$pageSize){
        $store = M("store");
        $member = M("Member");
        //查询店铺信息
        if($this->sessionInfo['parent_id'] == 0){
            $store_ids = $this->store_ids;
        }else{
            $store_ids = $this->sessionInfo['user_id'];
        }

        //查询所有部门信息
        $count = M("store_department")->where(array('store_id'=>array('in',$store_ids)))->count();
        $departmentList = M("store_department")->where(array('store_id'=>array('in',$store_ids)))->page($page,$pageSize)->select();
        $store_relationship = M("store_relationship");
        //声明每个部门的数组
        $departmentArr = array();
        $departmentSalesCount = 0;  //声明部门总销售量
        $departmentSalesMoney = 0;  //声明部门总销售额
        $json_department_money_arr = '';
        $json_department_count_arr = '';
        $total = 0;
        $arrPartment = array();
        foreach($departmentList as $key=>$value){
            //通过每个部门信息查询每个属于部门的员工
            $relationshipList = $store_relationship->where(array('department_id'=>$value['department_id']))->select();
            if(!empty($relationshipList)){
                //通过每个部门员工统计每个员工推广的用户  ，并查出销量及销售额
                $relationshipIds = '';

                foreach($relationshipList as $kk=>$item){
                    if($kk == 0){
                        $relationshipIds = $item['son_id'];
                    }else{
                        $relationshipIds .= ','.$item['son_id'];
                    }
                }
                $where['m.third_class'] = array('in',$relationshipIds);


                if($start_time){
                    $s_time = strtotime($start_time);
                    $where['o.pay_time'] = array("egt",$s_time);
                    $countWhere['reg_time'] = array("egt",$s_time);
                    if($end_time){
                        $e_time = strtotime($end_time);
                        $where['o.pay_time'] = array("between",array($s_time,$e_time));
                        $countWhere['reg_time'] = array("between",array($s_time,$e_time));
                    }
                }else if($end_time){
                    $e_time = strtotime($end_time);
                    $where['o.pay_time'] = array("elt",$e_time);
                    $countWhere['reg_time'] = array("elt",$e_time);
                }

                $countWhere['third_class'] = array("in",$relationshipIds);
                $countWhere['fourth_class'] = array("neq",0);
                $arrPartment[$key]['count'] = $member->where($countWhere)->count();


                $resultCompleted = $this->statisticalOrder($where,1); //已成交
                $resultDeal      = $this->statisticalOrder($where,2); //未成交 等待买家付款
                $resultCancel    = $this->statisticalOrder($where,3); //已取消
                $resultRefund    = $this->statisticalOrder($where,4); //退款订单


                $arrPartment[$key]['deal_count'] = $resultDeal['count']?$resultDeal['count']:0;
                $arrPartment[$key]['deal_money'] = $resultDeal['total']?$resultDeal['total']:0.00;
                $arrPartment[$key]['cancel_count'] = $resultCancel['count']?$resultCancel['count']:0;
                $arrPartment[$key]['cancel_money'] = $resultCancel['total']?$resultCancel['total']:0.00;
                $arrPartment[$key]['refund_count'] = $resultRefund['count']?$resultRefund['count']:0;
                $arrPartment[$key]['refund_money'] = $resultRefund['total']?$resultRefund['total']:0.00;
                $arrPartment[$key]['sales_count'] = $resultCompleted['count']?$resultCompleted['count']:0;
                $arrPartment[$key]['sales_money'] = $resultCompleted['total']?$resultCompleted['total']:0.00;
            }else{
                $arrPartment[$key]['deal_count'] = 0;
                $arrPartment[$key]['deal_money'] = 0.00;
                $arrPartment[$key]['cancel_count'] = 0;
                $arrPartment[$key]['cancel_money'] = 0.00;
                $arrPartment[$key]['refund_count'] = 0;
                $arrPartment[$key]['refund_money'] = 0.00;
                $arrPartment[$key]['sales_count'] = 0;
                $arrPartment[$key]['sales_money'] = 0.00;
            }
            //查询每个部门对应的店铺
            $storeInfo = $store->where(array('store_id'=>$value['store_id']))->find();
            $arrPartment[$key]['department_name'] = $value['department_name'];
            $arrPartment[$key]['store_name'] = $storeInfo['store_name'];
            $total += $arrPartment[$key]['sales_money'];
        }
        $last_names = array_column($arrPartment,'total');
        array_multisort($last_names,SORT_DESC,$arrPartment);
        $arrData = array("list"=>$arrPartment,'totalSales'=>$total,'count'=>$count);
        return $arrData;
    }

    //根据条件调取销量
    private function sales_count($start_time,$end_time,$page,$pageSize){
        $member = M("member");
        //判断当前登录人为总店铺身份
        if($this->sessionInfo['parent_id'] == 0){
            //查询店铺信息
            $count = M("store")->where(array('parent_id'=>$this->sessionInfo['user_id']))->count();
            $son_store = M("store")->where(array('parent_id'=>$this->sessionInfo['user_id']))->page($page,$pageSize)->select();
            $arr = array();
            $total_count = 0;   //声明总销量
            $total_money = 0;   //声明总销售额
            $storeTotalNum = '';
            $storeTotalMoney = '';
            $store_ids = '';
            foreach($son_store as $k=>$v){
                $where_main['m.second_class'] = $v['store_id'];
                if($start_time){
                    $s_time = strtotime($start_time);
                    $where_main['o.pay_time'] = array("egt",$s_time);
                    $countWhere['reg_time'] = array("egt",$s_time);
                    if($end_time){
                        $e_time = strtotime($end_time);
                        $where_main['o.pay_time'] = array("between",array($s_time,$e_time));
                        $countWhere['reg_time'] = array("between",array($s_time,$e_time));
                    }
                }else if($end_time){
                    $e_time = strtotime($end_time);
                    $where_main['o.pay_time'] = array("elt",$e_time);
                    $countWhere['reg_time'] = array("elt",$e_time);
                }

                $resultCompleted = $this->statisticalOrder($where_main,1); //已成交
                $resultDeal      = $this->statisticalOrder($where_main,2); //未成交 等待买家付款
                $resultCancel    = $this->statisticalOrder($where_main,3); //已取消
                $resultRefund    = $this->statisticalOrder($where_main,4); //退款订单

                $arr[$k]['store_id'] = $v['store_id'];
                $arr[$k]['store_name'] = $v['store_name'];

                $countWhere['second_class'] = $v['store_id'];
                $countWhere['fourth_class'] = array("neq",0);

                $arr[$k]['count'] = $member->where($countWhere)->count();
                $arr[$k]['deal_count'] = $resultDeal['count']?$resultDeal['count']:0;
                $arr[$k]['deal_money'] = $resultDeal['total']?$resultDeal['total']:0.00;
                $arr[$k]['cancel_count'] = $resultCancel['count']?$resultCancel['count']:0;
                $arr[$k]['cancel_money'] = $resultCancel['total']?$resultCancel['total']:0.00;
                $arr[$k]['refund_count'] = $resultRefund['count']?$resultRefund['count']:0;
                $arr[$k]['refund_money'] = $resultRefund['total']?$resultRefund['total']:0.00;
                $arr[$k]['sales_count'] = $resultCompleted['count']?$resultCompleted['count']:0;
                $arr[$k]['sales_money'] = $resultCompleted['total']?$resultCompleted['total']:0.00;
                $total_count += $arr[$k]['sales_money'];
            }
        }
        $last_names = array_column($arr,'sales_money');
        array_multisort($last_names,SORT_DESC,$arr);
        $arrData = array("list"=>$arr,'totalSales'=>$total_count,'count'=>$count);
        return $arrData;
    }

    //公共方法请求订单销量和销售金额
    public function excel(){

        $where = array();
        //时间参数
        $start_time = I('start_time');
        $end_time = I('end_time');
        $type = I('type');
        //拼接where条件
        if($start_time){
            $s_time = strtotime($start_time);
            $where['o.pay_time'] = array("egt",$s_time);
            if($end_time){
                $e_time = strtotime($end_time);
                $where['o.pay_time'] = array("between",array($s_time,$e_time));
            }
        }else if($end_time){
            $e_time = strtotime($end_time);
            $where['o.pay_time'] = array("elt",$e_time);
        }

        if($type == 1){
            $this->sonExcel($start_time,$end_time);
        }elseif($type == 2){
            $this->storeExcel($start_time,$end_time);
        }else{
            $this->departmentExcel($start_time,$end_time);
        }
    }
    private function departmentExcel($start_time=null,$end_time=null){
        $store = M("store");
        $member = M("member");
        //查询店铺信息
        if($this->sessionInfo['parent_id'] == 0){
            $store_ids = $this->store_ids;
        }else{
            $store_ids = $this->sessionInfo['user_id'];
        }

        //查询所有部门信息
        $departmentList = M("store_department")->where(array('store_id'=>array('in',$store_ids)))->select();
        $store_relationship = M("store_relationship");
        //声明每个部门的数组
        $departmentArr = array();
        $departmentSalesCount = 0;  //声明部门总销售量
        $departmentSalesMoney = 0;  //声明部门总销售额
        $json_department_money_arr = '';
        $json_department_count_arr = '';
        $total = 0;
        $count = 0;
        $totalNum = 0;

        $arrPartment = array();
        if($start_time){
            $s_time = strtotime($start_time);
            $where['o.pay_time'] = array("egt",$s_time);
            $countWhere['reg_time'] = array("egt",$s_time);
            if($end_time){
                $e_time = strtotime($end_time);
                $where['o.pay_time'] = array("between",array($s_time,$e_time));
                $countWhere['reg_time'] = array("between",array($s_time,$e_time));
            }
        }else if($end_time){
            $e_time = strtotime($end_time);
            $where['o.pay_time'] = array("elt",$e_time);
            $countWhere['reg_time'] = array("elt",$e_time);
        }
        foreach($departmentList as $key=>$value){
            //通过每个部门信息查询每个属于部门的员工
            $relationshipList = $store_relationship->where(array('department_id'=>$value['department_id']))->select();
            if(!empty($relationshipList)){
                //通过每个部门员工统计每个员工推广的用户  ，并查出销量及销售额
                $relationshipIds = '';

                foreach($relationshipList as $kk=>$item){
                    if($kk == 0){
                        $relationshipIds = $item['son_id'];
                    }else{
                        $relationshipIds .= ','.$item['son_id'];
                    }
                }
                $where['m.third_class'] = array('in',$relationshipIds);

                $resultCompleted = $this->statisticalOrder($where,1); //已成交
                $resultDeal      = $this->statisticalOrder($where,2); //未成交 等待买家付款
                $resultCancel    = $this->statisticalOrder($where,3); //已取消
                $resultRefund    = $this->statisticalOrder($where,4); //退款订单


                $countWhere['third_class'] = array("in",$relationshipIds);
                $countWhere['fourth_class'] = array("neq",0);
                $arrPartment[$key]['count'] = $member->where($countWhere)->count();

                $arrPartment[$key]['deal_count'] = $resultDeal['count']?$resultDeal['count']:0;
                $arrPartment[$key]['deal_money'] = $resultDeal['total']?$resultDeal['total']:0.00;
                $arrPartment[$key]['cancel_count'] = $resultCancel['count']?$resultCancel['count']:0;
                $arrPartment[$key]['cancel_money'] = $resultCancel['total']?$resultCancel['total']:0.00;
                $arrPartment[$key]['refund_count'] = $resultRefund['count']?$resultRefund['count']:0;
                $arrPartment[$key]['refund_money'] = $resultRefund['total']?$resultRefund['total']:0.00;
                $arrPartment[$key]['sales_count'] = $resultCompleted['count']?$resultCompleted['count']:0;
                $arrPartment[$key]['sales_money'] = $resultCompleted['total']?$resultCompleted['total']:0.00;
                $totalNum += $arrPartment[$key]['count'];
            }else{
                $arrPartment[$key]['deal_count'] = 0;
                $arrPartment[$key]['deal_money'] = 0.00;
                $arrPartment[$key]['cancel_count'] = 0;
                $arrPartment[$key]['cancel_money'] = 0.00;
                $arrPartment[$key]['refund_count'] = 0;
                $arrPartment[$key]['refund_money'] = 0.00;
                $arrPartment[$key]['sales_count'] = 0;
                $arrPartment[$key]['sales_money'] = 0.00;
            }
            //查询每个部门对应的店铺
            $storeInfo = $store->where(array('store_id'=>$value['store_id']))->find();
            $arrPartment[$key]['department_name'] = $value['department_name'];
            $arrPartment[$key]['store_name'] = $storeInfo['store_name'];

            $total += $arrPartment[$key]['sales_money'];
            $count += $arrPartment[$key]['sales_count'];
        }

        $last_names = array_column($arrPartment,'total');
        array_multisort($last_names,SORT_DESC,$arrPartment);

        require '../sellerserver/ThinkPHP/Library/Vendor/PHPExcel/PHPExcel.php';
        $excel = new \PHPExcel();
        $objActSheet = $excel->getActiveSheet();
        $objActSheet->setCellValue('A1', '部门名称');
        $objActSheet->setCellValue('B1', '店铺名称');
        $objActSheet->setCellValue('C1', '已成交数量');
        $objActSheet->setCellValue('D1', '已成交金额');
        $objActSheet->setCellValue('E1', '未成交数量');
        $objActSheet->setCellValue('F1', '未成交金额');
        $objActSheet->setCellValue('G1', '已取消订单数');
        $objActSheet->setCellValue('H1', '已取消订单金额');
        $objActSheet->setCellValue('I1', '已退款订单数');
        $objActSheet->setCellValue('J1', '已退款订单金额');
        $objActSheet->setCellValue('K1', '推广人数');

        $num = 0;
        foreach ($arrPartment as $k => $v){
            $num += 2;
            $k +=2;
            $time = date('Y-m-d H:i:s',$v['reg_time']);
            $objActSheet->setCellValue('A'.$k, $v['department_name']);
            $objActSheet->setCellValue('B'.$k, $v['store_name']);
            $objActSheet->setCellValue('C'.$k, $v['sales_count']);
            $objActSheet->setCellValue('D'.$k, $v['sales_money']);
            $objActSheet->setCellValue('E'.$k, $v['deal_count']);
            $objActSheet->setCellValue('F'.$k, $v['deal_money']);
            $objActSheet->setCellValue('G'.$k, $v['cancel_count']);
            $objActSheet->setCellValue('H'.$k, $v['cancel_money']);
            $objActSheet->setCellValue('I'.$k, $v['refund_count']);
            $objActSheet->setCellValue('J'.$k, $v['refund_money']);
            $objActSheet->setCellValue('K'.$k, $v['count']);

            $objActSheet->getRowDimension($k)->setRowHeight(15);//设置行高     放在前面设置有数据的行不生效
            $objActSheet->getColumnDimension('A')->setWidth(20);//设置G行宽度  放在前面设置有数据的行不生效
            $objActSheet->getColumnDimension('B')->setWidth(20);//设置G行宽度  放在前面设置有数据的行不生效
            $objActSheet->getColumnDimension('C')->setWidth(20);//设置G行宽度  放在前面设置有数据的行不生效
            $objActSheet->getColumnDimension('D')->setWidth(20);//设置G行宽度  放在前面设置有数据的行不生效
            $objActSheet->getColumnDimension('E')->setWidth(20);//设置G行宽度  放在前面设置有数据的行不生效
            $objActSheet->getColumnDimension('F')->setWidth(20);//设置G行宽度  放在前面设置有数据的行不生效
            $objActSheet->getColumnDimension('G')->setWidth(20);//设置G行宽度  放在前面设置有数据的行不生效
            $objActSheet->getColumnDimension('H')->setWidth(20);//设置G行宽度  放在前面设置有数据的行不生效
            $objActSheet->getColumnDimension('I')->setWidth(20);//设置G行宽度  放在前面设置有数据的行不生效
            $objActSheet->getColumnDimension('J')->setWidth(20);//设置G行宽度  放在前面设置有数据的行不生效
            $objActSheet->getColumnDimension('K')->setWidth(20);//设置G行宽度  放在前面设置有数据的行不生效
        }
        $objActSheet->setCellValue('A'.$num, '销量合计：'.$count);
        $objActSheet->setCellValue('B'.$num, '销售额合计：'.$total);
        $objActSheet->setCellValue('C'.$num, '推广人数总数：'.$totalNum);

        $excel = \PHPExcel_IOFactory::createWriter($excel, 'Excel2007');  //Excel2007  兼容好
        if(!$start_time)$start_time=date('Y-m-d');
        if(!$end_time)$end_time=date('Y-m-d');
        ob_end_clean();   //解决乱码
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename='.$start_time .'-'. $end_time .'销售数据'. '.xlsx');
        header('Cache-Control: max-age=0');
        $excel->save('php://output');
        exit;

    }

    private function storeExcel($start_time=null,$end_time=null){
        $member = D('member');
        $where_main['m.first_class'] = $this->sessionInfo['user_id'];


        //查询店铺信息
        $son_store = M("store")->where(array('parent_id'=>$this->sessionInfo['user_id']))->select();
        $arr = array();
        $total_count = 0;   //声明总销量
        $total_money = 0;   //声明总销售额
        $totalNum = 0;
        $storeTotalNum = '';
        $storeTotalMoney = '';
        $store_ids = '';
        foreach($son_store as $k=>$v){
            $where_main['m.second_class'] = $v['store_id'];
            if($start_time){
                $s_time = strtotime($start_time);
                $where_main['o.pay_time'] = array("egt",$s_time);
                $countWhere['reg_time'] = array("egt",$s_time);
                if($end_time){
                    $e_time = strtotime($end_time);
                    $where_main['o.pay_time'] = array("between",array($s_time,$e_time));
                    $countWhere['reg_time'] = array("between",array($s_time,$e_time));
                }
            }else if($end_time){
                $e_time = strtotime($end_time);
                $where_main['o.pay_time'] = array("elt",$e_time);
                $countWhere['reg_time'] = array("elt",$e_time);
            }
            $resultCompleted = $this->statisticalOrder($where_main,1); //已成交
            $resultDeal      = $this->statisticalOrder($where_main,2); //未成交 等待买家付款
            $resultCancel    = $this->statisticalOrder($where_main,3); //已取消
            $resultRefund    = $this->statisticalOrder($where_main,4); //退款订单

            $arr[$k]['store_id'] = $v['store_id'];
            $arr[$k]['store_name'] = $v['store_name'];

            $countWhere['second_class'] = $v['store_id'];
            $countWhere['fourth_class'] = array("neq",0);
            $arr[$k]['count'] = $member->where($countWhere)->count();
            $arr[$k]['deal_count'] = $resultDeal['count']?$resultDeal['count']:0;
            $arr[$k]['deal_money'] = $resultDeal['total']?$resultDeal['total']:0.00;
            $arr[$k]['cancel_count'] = $resultCancel['count']?$resultCancel['count']:0;
            $arr[$k]['cancel_money'] = $resultCancel['total']?$resultCancel['total']:0.00;
            $arr[$k]['refund_count'] = $resultRefund['count']?$resultRefund['count']:0;
            $arr[$k]['refund_money'] = $resultRefund['total']?$resultRefund['total']:0.00;
            $arr[$k]['sales_count'] = $resultCompleted['count']?$resultCompleted['count']:0;
            $arr[$k]['sales_money'] = $resultCompleted['total']?$resultCompleted['total']:0.00;
            $totalNum += $arr[$k]['count'];
            $total_count += $arr[$k]['sales_count'];
            $total_money += $arr[$k]['sales_money'];
        }
        $last_names = array_column($arr,'sales_money');
        array_multisort($last_names,SORT_DESC,$arr);


        require '../sellerserver/ThinkPHP/Library/Vendor/PHPExcel/PHPExcel.php';
        $excel = new \PHPExcel();
        $objActSheet = $excel->getActiveSheet();
        $objActSheet->setCellValue('A1', '店铺名称');
        $objActSheet->setCellValue('B1', '上级店铺');
        $objActSheet->setCellValue('C1', '已成交数量');
        $objActSheet->setCellValue('D1', '已成交金额');
        $objActSheet->setCellValue('E1', '未成交数量');
        $objActSheet->setCellValue('F1', '未成交金额');
        $objActSheet->setCellValue('G1', '已取消订单数');
        $objActSheet->setCellValue('H1', '已取消订单金额');
        $objActSheet->setCellValue('I1', '已退款订单数');
        $objActSheet->setCellValue('J1', '已退款订单金额');
        $objActSheet->setCellValue('K1', '推广人数');

        $num = 0;
        foreach ($arr as $k => $v){
            $num += 2;
            $k +=2;
            $time = date('Y-m-d H:i:s',$v['reg_time']);
            $objActSheet->setCellValue('A'.$k, $v['store_name']);
            $objActSheet->setCellValue('B'.$k, $this->sessionInfo['store_name']);
            $objActSheet->setCellValue('C'.$k, $v['sales_count']);
            $objActSheet->setCellValue('D'.$k, $v['sales_money']);
            $objActSheet->setCellValue('E'.$k, $v['deal_count']);
            $objActSheet->setCellValue('F'.$k, $v['deal_money']);
            $objActSheet->setCellValue('G'.$k, $v['cancel_count']);
            $objActSheet->setCellValue('H'.$k, $v['cancel_money']);
            $objActSheet->setCellValue('I'.$k, $v['refund_count']);
            $objActSheet->setCellValue('J'.$k, $v['refund_money']);
            $objActSheet->setCellValue('K'.$k, $v['count']);

            $objActSheet->getRowDimension($k)->setRowHeight(15);//设置行高     放在前面设置有数据的行不生效
            $objActSheet->getColumnDimension('A')->setWidth(20);//设置G行宽度  放在前面设置有数据的行不生效
            $objActSheet->getColumnDimension('B')->setWidth(20);//设置G行宽度  放在前面设置有数据的行不生效
            $objActSheet->getColumnDimension('C')->setWidth(20);//设置G行宽度  放在前面设置有数据的行不生效
            $objActSheet->getColumnDimension('D')->setWidth(20);//设置G行宽度  放在前面设置有数据的行不生效
            $objActSheet->getColumnDimension('E')->setWidth(20);//设置G行宽度  放在前面设置有数据的行不生效
            $objActSheet->getColumnDimension('F')->setWidth(20);//设置G行宽度  放在前面设置有数据的行不生效
            $objActSheet->getColumnDimension('G')->setWidth(20);//设置G行宽度  放在前面设置有数据的行不生效
            $objActSheet->getColumnDimension('H')->setWidth(20);//设置G行宽度  放在前面设置有数据的行不生效
            $objActSheet->getColumnDimension('I')->setWidth(20);//设置G行宽度  放在前面设置有数据的行不生效
            $objActSheet->getColumnDimension('J')->setWidth(20);//设置G行宽度  放在前面设置有数据的行不生效
            $objActSheet->getColumnDimension('K')->setWidth(20);//设置G行宽度  放在前面设置有数据的行不生效
        }
        $countArr = count($arr)+2;
        $objActSheet->setCellValue('A'.$countArr, '销量合计：'.$total_count);
        $objActSheet->setCellValue('B'.$countArr, '销售额合计：'.$total_money);
        $objActSheet->setCellValue('C'.$countArr, '推广人数总数：'.$totalNum);

        $excel = \PHPExcel_IOFactory::createWriter($excel, 'Excel2007');  //Excel2007  兼容好
        if(!$start_time)$start_time=date('Y-m-d');
        if(!$end_time)$end_time=date('Y-m-d');
        ob_end_clean();   //解决乱码
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename='.$start_time .'-'. $end_time .'销售数据'. '.xlsx');
        header('Cache-Control: max-age=0');
        $excel->save('php://output');
        exit;

    }

    private function sonExcel($start_time=null,$end_time=null){

        if($start_time){
            $s_time = strtotime($start_time);
            $where['o.pay_time'] = array("egt",$s_time);
            $countWhere['reg_time'] = array("egt",$s_time);
            if($end_time){
                $e_time = strtotime($end_time);
                $where['o.pay_time'] = array("between",array($s_time,$e_time));
                $countWhere['reg_time'] = array("between",array($s_time,$e_time));
            }
        }else if($end_time){
            $e_time = strtotime($end_time);
            $where['o.pay_time'] = array("elt",$e_time);
            $countWhere['reg_time'] = array("elt",$e_time);
        }

        $storeInfo = M("store")->where(array("store_id"=>$this->sessionInfo['user_id']))->find();
        $relationship = M("store_relationship");
        $member = M("member");
        $con = array(
            'ecm_store_relationship.store_id' => $this->sessionInfo['user_id'] ,
        );
        //说明登录人为总店铺
        if($storeInfo['parent_id'] == 0){
            $con = array('ecm_store_relationship.parent_id'=>$this->sessionInfo['user_id']);
        }

        $list = $relationship
            ->join("join ecm_member on ecm_store_relationship.son_id = ecm_member.user_id")
            ->join("join ecm_store on ecm_store_relationship.parent_id = ecm_store.store_id")
            ->field("ecm_member.*,ecm_store.store_name,ecm_store_relationship.image,ecm_store_relationship.department_name,ecm_store_relationship.store_id")
            ->where($con)->select();
        if(!empty($list)){
            $count_arr = 0;
            $total_arr = 0.00;
            $totalNum = 0;

            foreach($list as $k=>$v){
                $where['m.third_class'] = $v['user_id'];
                $resultCompleted = $this->statisticalOrder($where,1); //已成交
                $resultDeal      = $this->statisticalOrder($where,2); //未成交 等待买家付款
                $resultCancel    = $this->statisticalOrder($where,3); //已取消
                $resultRefund    = $this->statisticalOrder($where,4); //退款订单

                $countWhere['fourth_class'] = $v['user_id'];
                $list[$k]['count'] = $member->where($countWhere)->count();

                $list[$k]['deal_count'] = $resultDeal['count']?$resultDeal['count']:0;
                $list[$k]['deal_money'] = $resultDeal['total']?$resultDeal['total']:0.00;
                $list[$k]['cancel_count'] = $resultCancel['count']?$resultCancel['count']:0;
                $list[$k]['cancel_money'] = $resultCancel['total']?$resultCancel['total']:0.00;
                $list[$k]['refund_count'] = $resultRefund['count']?$resultRefund['count']:0;
                $list[$k]['refund_money'] = $resultRefund['total']?$resultRefund['total']:0.00;
                $list[$k]['sales_count'] = $resultCompleted['count']?$resultCompleted['count']:0;
                $list[$k]['sales_money'] = $resultCompleted['total']?$resultCompleted['total']:0.00;

                $totalNum += $list[$k]['count'];
                $count_arr += $list[$k]['sales_count'];
                $total_arr += $list[$k]['sales_money'];
            }

            require '../sellerserver/ThinkPHP/Library/Vendor/PHPExcel/PHPExcel.php';
            $excel = new \PHPExcel();
            $objActSheet = $excel->getActiveSheet();
            $objActSheet->setCellValue('A1', '员工名称');
            $objActSheet->setCellValue('B1', '所属店铺');
            $objActSheet->setCellValue('C1', '所属部门');
            $objActSheet->setCellValue('D1', '已成交数量');
            $objActSheet->setCellValue('E1', '已成交金额');
            $objActSheet->setCellValue('F1', '未成交数量');
            $objActSheet->setCellValue('G1', '未成交金额');
            $objActSheet->setCellValue('H1', '已取消订单数');
            $objActSheet->setCellValue('I1', '已取消订单金额');
            $objActSheet->setCellValue('J1', '已退款订单数');
            $objActSheet->setCellValue('K1', '已退款订单金额');
            $objActSheet->setCellValue('L1', '推广人数');

            $num = 0;
            foreach ($list as $k => $v){
                $num += 2;
                $k +=2;
                $time = date('Y-m-d H:i:s',$v['reg_time']);
                $objActSheet->setCellValue('A'.$k, $v['real_name']);
                $objActSheet->setCellValue('B'.$k, $storeInfo['store_name']);
                $objActSheet->setCellValue('C'.$k, $v['department_name']?$v['department_name']:'无');
                $objActSheet->setCellValue('D'.$k, $v['sales_count']);
                $objActSheet->setCellValue('E'.$k, $v['sales_money']);
                $objActSheet->setCellValue('F'.$k, $v['deal_count']);
                $objActSheet->setCellValue('G'.$k, $v['deal_money']);
                $objActSheet->setCellValue('H'.$k, $v['cancel_count']);
                $objActSheet->setCellValue('I'.$k, $v['cancel_money']);
                $objActSheet->setCellValue('J'.$k, $v['refund_count']);
                $objActSheet->setCellValue('K'.$k, $v['refund_money']);
                $objActSheet->setCellValue('L'.$k, $v['count']);

                $objActSheet->getRowDimension($k)->setRowHeight(15);//设置行高     放在前面设置有数据的行不生效
                $objActSheet->getColumnDimension('A')->setWidth(20);//设置G行宽度  放在前面设置有数据的行不生效
                $objActSheet->getColumnDimension('B')->setWidth(20);//设置G行宽度  放在前面设置有数据的行不生效
                $objActSheet->getColumnDimension('C')->setWidth(20);//设置G行宽度  放在前面设置有数据的行不生效
                $objActSheet->getColumnDimension('D')->setWidth(20);//设置G行宽度  放在前面设置有数据的行不生效
                $objActSheet->getColumnDimension('E')->setWidth(20);//设置G行宽度  放在前面设置有数据的行不生效
                $objActSheet->getColumnDimension('F')->setWidth(20);//设置G行宽度  放在前面设置有数据的行不生效
                $objActSheet->getColumnDimension('G')->setWidth(20);//设置G行宽度  放在前面设置有数据的行不生效
                $objActSheet->getColumnDimension('H')->setWidth(20);//设置G行宽度  放在前面设置有数据的行不生效
                $objActSheet->getColumnDimension('I')->setWidth(20);//设置G行宽度  放在前面设置有数据的行不生效
                $objActSheet->getColumnDimension('J')->setWidth(20);//设置G行宽度  放在前面设置有数据的行不生效
                $objActSheet->getColumnDimension('K')->setWidth(20);//设置G行宽度  放在前面设置有数据的行不生效
            }
            $num1 = count($list)+2;
            $objActSheet->setCellValue('A'.$num1, '销量合计：'.$totalNum);
            $objActSheet->setCellValue('B'.$num1, '销售额合计：'.$total_arr);
            $objActSheet->setCellValue('B'.$num1, '销售额合计：'.$total_arr);

            $excel = \PHPExcel_IOFactory::createWriter($excel, 'Excel2007');  //Excel2007  兼容好
            if(!$start_time)$start_time=date('Y-m-d');
            if(!$end_time)$end_time=date('Y-m-d');
            ob_end_clean();   //解决乱码
            header('Content-Type: application/vnd.ms-excel');
            header('Content-Disposition: attachment;filename='.$start_time .'-'. $end_time .'销售数据'. '.xlsx');
            header('Cache-Control: max-age=0');
            $excel->save('php://output');
            exit;
        }else{
            $this->error('所选数据为空！');
        }
    }


}

function getAllDate($startDate,$endDate){
    $date = '';
    $startTime = strtotime($startDate);
    $endTime = strtotime($endDate);

    $middle = $startTime;

    while ( $middle <= $endTime ) {
        $date .= date('Y-m-d',$middle) . ',';
        $middle = strtotime('+1 day',$middle);
    }
    $date = substr($date,0,strlen($date)-1);
    return $date;
}